package org.kl.bf.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Random;

import javax.servlet.ServletOutputStream;

import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.PropertyUtils;
import org.apache.commons.lang3.ArrayUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.commons.lang3.builder.ToStringBuilder;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.kl.bf.entity.Excel;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

/**
 * @Author hqm
 * @CreateTime 2014年4月28日 下午8:05:46
 * 
 *             ChangeLog : 2015-03-30 QJ 扩展数字格式 2015-04-01 QJ 扩展excel样式
 */
public class ExcelWriter {
	private static final int sheetMaxRowCount = 65000;
	private static Logger logger = LoggerFactory.getLogger(ExcelWriter.class);
	private static final String ROW_STYLER_METHOD_KEY = "rowStyler";

	public static void exportExcel(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out) {
		// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
		exportExcelInUserModel(title, pojoClass, dataSet, out);
		// 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
	}

	public static void exportExcelInclude(String title, Class<?> pojoClass, Collection<?> dataSet, List<String> includeField,
			OutputStream out) {
		// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
		exportExcelIncludeInUserModel(title, pojoClass, dataSet, includeField, out);
		// 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
	}

	/**
	 * 导出excel
	 * 
	 * @description 支持List<String[]>类型数据导出，而不必关联实体类
	 * @author QJ 2014-12-22
	 * @param title
	 * @param data
	 * @param out
	 */
	public static void exportExcel(String title, List<String[]> data, OutputStream out) {
		// 检查数据正确性
		try {
			if ((data == null) || (data.size() == 0)) {
				throw new Exception("导出数据为空！");
			}
			// 声明一个工作薄
			Workbook workbook = new HSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			for (int index = 0; index < data.size(); index++) {
				Row row = sheet.createRow(index);
				String[] dataRow = data.get(index);
				for (int i = 0; i < dataRow.length; i++) {
					Cell cell = row.createCell(i);
					String value = dataRow[i];
					if (isNumbericString(value)) {
						//excel中全数字形式的字段超过11位会丢失最后几位数
						if(value.length()>=11){
							cell.setCellType(Cell.CELL_TYPE_STRING);
							cell.setCellValue(value);
						}else{
							cell.setCellType(Cell.CELL_TYPE_NUMERIC);
							cell.setCellValue(new Double(value.toString()));
						}
					} else {
						RichTextString text = new HSSFRichTextString(value);
						cell.setCellValue(text);
					}

				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static void exportExcelWithFields(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out, Field[] fields) {
		// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
		exportExcelInUserModelWithFields(title, pojoClass, dataSet, out, fields);
		// 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
	}

	private static void exportExcelInUserModel(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out) {
		try {
			// 首先检查数据看是否是正确的
			if ((dataSet == null) || (dataSet.size() == 0)) {
				throw new Exception("导出数据为空！");
			}
			if ((title == null) || (out == null) || (pojoClass == null)) {
				throw new Exception("传入参数不能为空！");
			}
			if (dataSet.size() > sheetMaxRowCount) {
				throw new Exception("需要导出的内容过多，请调整后导出");
			}
			// 声明一个工作薄
			Workbook workbook = new HSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			// 标题
			List<String> exportFieldTitle = new ArrayList<String>();
			List<Integer> exportFieldWidth = new ArrayList<Integer>();// 列宽
			List<String> exportFields = new ArrayList<String>();// 属性集
			List<Object> exportFieldTypes = new ArrayList<Object>();
			List<String> exportFormats = new ArrayList<String>();

			// 拿到所有列名，以及导出的字段的get方法
			Map<String, Method> convertMethod = new HashMap<String, Method>();

			// 得到所有字段
			Field[] fields = pojoClass.getDeclaredFields();
			Class<?> superClz = pojoClass.getSuperclass();
			if (superClz != null) {
				fields = ArrayUtils.addAll(fields, superClz.getDeclaredFields());
			}
			// 遍历整个filed
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				Excel excel = field.getAnnotation(Excel.class);
				// 如果设置了annottion
				if (excel != null) {
					// 添加到需要导出的字段的方法
					String fieldname = field.getName();
					if (StringUtils.isBlank(excel.exportProperty())) {
						// 添加到标题
						exportFieldTitle.add(excel.exportName());
						// 添加标题的列宽
						exportFieldWidth.add(Integer.parseInt(excel.exportFieldWidth()));
						// 添加列的属性
						exportFields.add(fieldname);
						exportFieldTypes.add(field.getType());
						exportFormats.add(excel.exportFormat());
					} else {
						String[] ens = StringUtils.split(excel.exportName(), "|");
						String[] efws = StringUtils.split(excel.exportFieldWidth(), "|");
						String[] eps = StringUtils.split(excel.exportProperty(), "|");
						// 获取类里面到处字段的类型
						Field subField = field;
						for (int j = 0; j < ens.length; j++) {
							for (String fieldName : eps[j].split(".")) {
								Class<?> subclass = subField.getType();
								subField = subclass.getDeclaredField(fieldName);
							}
							exportFieldTitle.add(ens[j]);
							exportFieldWidth.add(Integer.parseInt(efws[efws.length - 1 > j ? j : efws.length - 1]));
							exportFields.add(fieldname + "." + eps[j]);
							exportFieldTypes.add(subField.getType());
							exportFormats.add(excel.exportFormat());
						}
					}

					if (excel.exportConvertSign() == 1) {
						StringBuffer getConvertMethodName = new StringBuffer();
						getConvertMethodName.append(fieldname);
						getConvertMethodName.append("Convert");
						String methodName = getConvertMethodName.toString();
						/**
						 * 如果属性名称已“is”开头，启动报错，将其转换方法改为去掉is后的方法名称
						 * 2015-10-23 14:20
						 */
						if (StringUtils.startsWith(methodName, "is")) {
							methodName = methodName.substring(2, methodName.length());
							methodName = StringUtils.uncapitalize(methodName);
						}
						Method getConvertMethod = pojoClass.getMethod(methodName, new Class[] {});
						convertMethod.put(fieldname, getConvertMethod);
					}
				}
			}

			int index = 0;

			// 产生表格标题行
			Row row = sheet.createRow(index);

			for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
				Cell cell = row.createCell(i);
				RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
				cell.setCellValue(text);
			}

			// 设置每行的列宽
			for (int i = 0; i < exportFieldWidth.size(); i++) {
				// 256=65280/255
				sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
			}

			Iterator<?> dataIterator = dataSet.iterator();
			// 循环插入剩下的集合
			while (dataIterator.hasNext()) {
				// 从第二行开始写，第一行是标题
				index++;
				row = sheet.createRow(index);
				Object data = dataIterator.next();
				StyleBuilder styleBuilder = null;

				try {
					Method rowStylerMethod = pojoClass.getMethod(ROW_STYLER_METHOD_KEY, new Class[] {});
					styleBuilder = (StyleBuilder) rowStylerMethod.invoke(data);
				} catch (NoSuchMethodException exception) {
					// 找不到方法时，忽略
				}
				for (int k = 0, fSize = exportFields.size(); k < fSize; k++) {
					Cell cell = row.createCell(k);
					Object value = null;
					if (convertMethod.containsKey(exportFields.get(k))) {
						Method cm = convertMethod.get(exportFields.get(k));
						value = cm.invoke(data, new Object[] {});
					} else {
						try {
							value = StringUtils.defaultString(BeanUtils.getNestedProperty(data, exportFields.get(k)));
						} catch (Exception e) {
							value = "";
							logger.warn("第" + k + "列中" + exportFields.get(k) + "数据为空，" + ToStringBuilder.reflectionToString(data));
							e.printStackTrace();
						}
					}
					if (exportFieldTypes.get(k).equals(BigDecimal.class) && !value.equals(null)) {

						cell.setCellType(Cell.CELL_TYPE_NUMERIC);
						if (exportFormats.get(k).equals("万")) {
							cell.setCellValue(new BigDecimal(StringUtils.isEmpty((String) value) ? "0" : value.toString())
									.divide(new BigDecimal(10000)).setScale(6).doubleValue());
						} else {
							cell.setCellValue(new Double(StringUtils.isEmpty((String) value) ? "0" : value.toString()));
						}

					} else if (exportFieldTypes.get(k).equals(Date.class) && !value.equals(null) && !value.equals("")) {
						String format = exportFormats.get(k);
						if (format.equals("yyyy-MM-dd HH:mm:ss") && value.toString().length() > 19) {
							cell.setCellValue(value.toString().substring(0, 19));
						} else if (value.toString().length() > 10) {
							cell.setCellValue(value.toString().substring(0, 10));
						} else {
							cell.setCellValue(value.toString());
						}

					} else {
						cell.setCellValue(value.toString());
					}
					if (styleBuilder != null) {
						CellStyle cellStyle = styleBuilder.buildCellStyle(workbook);
						cell.setCellStyle(cellStyle);
					}
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	private static void exportExcelInUserModelWithFields(String title, Class<?> pojoClass, Collection<?> dataSet, OutputStream out,
			Field[] fields) {
		try {
			// 首先检查数据看是否是正确的
			if ((dataSet == null) || (dataSet.size() == 0)) {
				throw new Exception("导出数据为空！");
			}
			if ((title == null) || (out == null) || (pojoClass == null)) {
				throw new Exception("传入参数不能为空！");
			}
			if (dataSet.size() > sheetMaxRowCount) {
				throw new Exception("需要导出的内容过多，无法导出");
			}
			// 声明一个工作薄
			Workbook workbook = new HSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			// 标题
			List<String> exportFieldTitle = new ArrayList<String>();
			List<Integer> exportFieldWidth = new ArrayList<Integer>();

			// 拿到所有列名，以及导出的字段的get方法
			List<Method> methodObj = new ArrayList<Method>();
			Map<String, Method> convertMethod = new HashMap<String, Method>();

			// 得到所有字段
			/*
			 * Field[] Classfields = pojoClass.getDeclaredFields(); Class<?>
			 * superClz = pojoClass.getSuperclass(); if (superClz != null) {
			 * Classfields = ArrayUtils.addAll(fields,
			 * superClz.getDeclaredFields()); }
			 */
			// 遍历整个filed
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				Excel excel = field.getAnnotation(Excel.class);

				// 如果设置了annottion
				if (excel != null) {
					// 添加到标题
					exportFieldTitle.add(excel.exportName());
					// 添加标题的列宽
					exportFieldWidth.add(Integer.parseInt(excel.exportFieldWidth()));

					// 添加到需要导出的字段的方法
					String fieldname = field.getName();

					// System.out.println(i+"列宽"+excel.exportName()+" "+excel.exportFieldWidth());
					StringBuffer getMethodName = new StringBuffer("get");
					getMethodName.append(fieldname.substring(0, 1).toUpperCase());
					getMethodName.append(fieldname.substring(1));

					Method getMethod = pojoClass.getMethod(getMethodName.toString(), new Class[] {});

					methodObj.add(getMethod);

					if (excel.exportConvertSign() == 1) {
						StringBuffer getConvertMethodName = new StringBuffer();
						getConvertMethodName.append(fieldname);
						getConvertMethodName.append("Convert");
						Method getConvertMethod = pojoClass.getMethod(getConvertMethodName.toString(), new Class[] {});
						convertMethod.put(getMethodName.toString(), getConvertMethod);
					}
				}
			}

			int index = 0;

			// 产生表格标题行
			Row row = sheet.createRow(index);

			for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
				Cell cell = row.createCell(i);

				// cell.setCellStyle(style);
				RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
				cell.setCellValue(text);
			}

			// 设置每行的列宽
			for (int i = 0; i < exportFieldWidth.size(); i++) {
				// 256=65280/255
				sheet.setColumnWidth(i, 256 * new Integer(exportFieldWidth.get(i)));
			}

			Iterator<?> its = dataSet.iterator();

			// 循环插入剩下的集合
			while (its.hasNext()) {
				// 从第二行开始写，第一行是标题
				index++;
				row = sheet.createRow(index);

				Object t = its.next();

				for (int k = 0, methodObjSize = methodObj.size(); k < methodObjSize; k++) {
					Cell cell = row.createCell(k);
					Method getMethod = methodObj.get(k);
					Object value = null;

					if (convertMethod.containsKey(getMethod.getName())) {
						Method cm = convertMethod.get(getMethod.getName());
						value = cm.invoke(t, new Object[] {});
					} else {
						value = getMethod.invoke(t, new Object[] {});
					}
					cell.setCellValue(value == null ? "" : value.toString());
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	public static void exportExcelMoreSheet(String title, Class<?> pojoClass, Collection<?> dataSet, Class<?> pojoClass2, Collection<?> dataSet2,String title2,Class<?> pojoClass3, Collection<?> dataSet3,String title3,Class<?> pojoClass4, Collection<?> dataSet4,String title4,Class<?> pojoClass5, Collection<?> dataSet5,String title5,  OutputStream out) {
		// 使用userModel模式实现的，当excel文档出现10万级别的大数据文件可能导致OOM内存溢出
		exportExcelInUserModelMoreSheet(title, pojoClass, dataSet, pojoClass2, dataSet2,title2,pojoClass3, dataSet3,title3,pojoClass4, dataSet4,title4,pojoClass5, dataSet5,title5,out);
		// 使用eventModel实现，可以一边读一边处理，效率较高，但是实现复杂，暂时未实现
	}
	
	private static void exportExcelInUserModelMoreSheet(String title, Class<?> pojoClass, Collection<?> dataSet, Class<?> pojoClass2, Collection<?> dataSet2,String title2,Class<?> pojoClass3, Collection<?> dataSet3,String title3,Class<?> pojoClass4, Collection<?> dataSet4,String title4,Class<?> pojoClass5, Collection<?> dataSet5,String title5,  OutputStream out) {
		try{
			// 首先检查数据看是否是正确的
			if ((dataSet == null) || (dataSet.size() == 0)) {
				throw new Exception("导出数据为空！");
			}
			if ((title == null) || (out == null) || (pojoClass == null)) {
				throw new Exception("传入参数不能为空！");
			}
			if (dataSet.size() > sheetMaxRowCount) {
				throw new Exception("需要导出的内容过多，无法导出");
			}
			// 声明一个工作薄
			Workbook workbook = new HSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			Sheet sheet2 = workbook.createSheet(title2);
			Sheet sheet3 = workbook.createSheet(title3);
			Sheet sheet4 = workbook.createSheet(title4);
			Sheet sheet5 = workbook.createSheet(title5);
			
			Sheet(sheet, pojoClass,  dataSet );
			Sheet(sheet2, pojoClass2,  dataSet2 );
			Sheet(sheet3, pojoClass3,  dataSet3 );
			Sheet(sheet4, pojoClass4,  dataSet4 );
			Sheet(sheet5, pojoClass5,  dataSet5 );
			
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
	
	private static void Sheet(Sheet sheet,Class<?> pojoClass, Collection<?> dataSet ){
		try {// 标题
					List<String> exportFieldTitle = new ArrayList<String>();
					List<Integer> exportFieldWidth = new ArrayList<Integer>();// 列宽
					List<String> exportFields = new ArrayList<String>();// 属性集

					// 拿到所有列名，以及导出的字段的get方法
					Map<String, Method> convertMethod = new HashMap<String, Method>();

					// 得到所有字段
					Field[] fields = pojoClass.getDeclaredFields();
					Class<?> superClz = pojoClass.getSuperclass();
					if (superClz != null) {
						fields = ArrayUtils.addAll(fields, superClz.getDeclaredFields());
					}
					// 遍历整个filed
					for (int i = 0; i < fields.length; i++) {
						Field field = fields[i];
						Excel excel = field.getAnnotation(Excel.class);

						// 如果设置了annottion
						if (excel != null) {
							// 添加到需要导出的字段的方法
							String fieldname = field.getName();
							if (StringUtils.isBlank(excel.exportProperty())) {
								// 添加到标题
								exportFieldTitle.add(excel.exportName());
								// 添加标题的列宽
								exportFieldWidth.add(Integer.parseInt(excel.exportFieldWidth()));
								// 添加列的属性
								exportFields.add(fieldname);
							} else {
								String[] ens = StringUtils.split(excel.exportName(), "|");
								String[] efws = StringUtils.split(excel.exportFieldWidth(), "|");
								String[] eps = StringUtils.split(excel.exportProperty(), "|");
								for (int j = 0; j < ens.length; j++) {
									exportFieldTitle.add(ens[j]);
									exportFieldWidth.add(Integer.parseInt(efws[efws.length - 1 > j ? j : efws.length - 1]));
									exportFields.add(fieldname + "." + eps[j]);
								}
							}

							if (excel.exportConvertSign() == 1) {
								StringBuffer getConvertMethodName = new StringBuffer();
								getConvertMethodName.append(fieldname);
								getConvertMethodName.append("Convert");
								
								String methodName = getConvertMethodName.toString();
								/**
								 * 如果属性名称已“is”开头，启动报错，将其转换方法改为去掉is后的方法名称
								 * 2015-10-23 14:20
								 */
								if (StringUtils.startsWith(methodName, "is")) {
									methodName = methodName.substring(2, methodName.length());
									methodName = StringUtils.uncapitalize(methodName);
								}
								Method getConvertMethod = pojoClass.getMethod(methodName, new Class[] {});
								convertMethod.put(fieldname, getConvertMethod);
							}
						}
					}

					int index = 0;

					// 产生表格标题行
					Row row = sheet.createRow(index);

					for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
						Cell cell = row.createCell(i);
						RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
						cell.setCellValue(text);
					}

					// 设置每行的列宽
					for (int i = 0; i < exportFieldWidth.size(); i++) {
						// 256=65280/255
						sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
					}

					Iterator<?> dataIterator = dataSet.iterator();
					// 循环插入剩下的集合
					while (dataIterator.hasNext()) {
						// 从第二行开始写，第一行是标题
						index++;
						row = sheet.createRow(index);
						Object data = dataIterator.next();
						for (int k = 0, fSize = exportFields.size(); k < fSize; k++) {
							Cell cell = row.createCell(k);
							Object value = null;
							if (convertMethod.containsKey(exportFields.get(k))) {
								String methodName = exportFields.get(k);
								Method cm = convertMethod.get(methodName);
								value = cm.invoke(data, new Object[] {});
							} else {
								try {
									value = PropertyUtils.getNestedProperty(data, exportFields.get(k));
								} catch (Exception e) {
									logger.error("第" + k + "列中" + exportFields.get(k) + "数据为空，"
											+ ToStringBuilder.reflectionToString(data));
									// e.printStackTrace();
								}
							}
							if (value instanceof Double) {
								cell.setCellValue(value == null ? 0 : (Double) value);
							} else if (value instanceof Integer) {
								cell.setCellValue(value == null ? 0 : (Integer) value);
							} else {
								cell.setCellValue(value == null ? "" : value.toString());
							}
						}
					}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 根据Excel模板来导出Excel数据
	 */
	public static void excelExportWithExcelModel(String fileName, Integer startRowIndex, List<String[]> data, OutputStream out) {
		try {
			String path = BasicConstants.EXCEL_TEMPLATE + "\\" + fileName + ".xls";
			File fi = new File(path);
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
			HSSFWorkbook workbook = new HSSFWorkbook(fs);
			HSSFSheet sheet = workbook.getSheetAt(0);
			if (data.size() > 0) {
				HSSFCell cell[] = new HSSFCell[data.get(0).length];
				// 填充数据
				for (int i = 0; i < data.size(); i++) {
					HSSFRow row = sheet.createRow(startRowIndex + i);
					for (int j = 0; j < data.get(i).length; j++) {
						cell[j] = row.createCell(j);
						cell[j] = sheet.getRow(startRowIndex + i).getCell(j);
//						cell[j].setCellStyle(getCellStyle(workbook));
						String value = (String) data.get(i)[j];
						if (isNumbericString(value)) {
							cell[j].setCellType(Cell.CELL_TYPE_NUMERIC);
							cell[j].setCellValue(new Double(value));
						} else {
							cell[j].setCellType(Cell.CELL_TYPE_STRING);
							cell[j].setCellValue(value);
						}
					}
				}
				workbook.write(out);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 设置单元格格式
	 * 
	 * @param wb
	 * @return
	 */
	public static HSSFCellStyle getCellStyle(HSSFWorkbook wb) {
		HSSFCellStyle cellStyle = wb.createCellStyle();
		// 设置单元格边框
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);// 左边框
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);// 上边框
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);// 右边框
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN); // 下边框
		return cellStyle;
	}

	/**
	 * 导出excel失败清单
	 * 
	 * @param errorList
	 * @return
	 * @throws FileNotFoundException
	 * @throws IOException
	 */
	public static String exportErrorList(List<String[]> errorList) throws FileNotFoundException, IOException {
		SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");
		String errxls = df.format(new Date()) + "_" + new Random().nextInt(1000) + ".xls";
		String path = BasicConstants.EXCEL_ERROR_FOLDER + File.separator + errxls;
		File xlsFile = new File(path);
		FileOutputStream fo = new FileOutputStream(xlsFile);
		try {
			ExcelWriter.exportExcel("导入失败清单", errorList, fo);
		} finally {
			fo.close();
		}
		return errxls;
	}

	private static boolean isNumbericString(String value) {
		if (StringUtils.isBlank(value)) {
			return false;
		}
		return NumberUtils.isNumber(value.toString()) && !value.toString().matches("0[1-9]+");
	}

	/**
	 * 根据模板里需替换的内容，替换模板并导出
	 * 
	 * @author lzx
	 */
	@SuppressWarnings({ "rawtypes", "deprecation" })
	public static void excelExportReplaceExcelModel(String fileName, Integer startRowIndex, Map<String, Object> replaceData,
			ServletOutputStream out) {
		try {
			String path = BasicConstants.EXCEL_TEMPLATE + "\\" + fileName + ".xls";
			File fi = new File(path);
			POIFSFileSystem fs = new POIFSFileSystem(new FileInputStream(fi));
			HSSFWorkbook workbook = new HSSFWorkbook(fs);
			HSSFSheet sheet = workbook.getSheetAt(0);
			if (replaceData.size() > 0) {
				int rowNum = sheet.getLastRowNum() + 1;
				for (int i = 1; i < rowNum; i++) {
					HSSFRow row = sheet.getRow(i);
					if (row != null) {
						for (Iterator cellIt = row.cellIterator(); cellIt.hasNext();) {
							HSSFCell cell = (HSSFCell) cellIt.next();
							String cellValue = cell.toString();
							String key = "";
							if (cellValue != null && cellValue.trim().matches("[<].*?[>]")) {
								key = cellValue.replaceAll("[<](.*?)[>]", "$1");
								if (!replaceData.containsKey(key)) {
									sheet.getRow(i).getCell(cell.getCellNum()).setCellValue("");
									continue;
								}
								String value = replaceData.get(key).toString();
								if (value.equals("0")) {
									sheet.getRow(i).getCell(cell.getCellNum()).setCellValue("");
								} else {
									sheet.getRow(i).getCell(cell.getCellNum()).setCellValue(value);
								}
							}
						}
					}
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	// 导出需要的那些列
	private static void exportExcelIncludeInUserModel(String title, Class<?> pojoClass, Collection<?> dataSet, List<String> includeField,
			OutputStream out) {
		try {
			// 首先检查数据看是否是正确的
			if ((dataSet == null) || (dataSet.size() == 0)) {
				throw new Exception("导出数据为空！");
			}
			if ((title == null) || (out == null) || (pojoClass == null)) {
				throw new Exception("传入参数不能为空！");
			}
			if (dataSet.size() > sheetMaxRowCount) {
				throw new Exception("需要导出的内容过多，无法导出");
			}
			// 声明一个工作薄
			Workbook workbook = new HSSFWorkbook();
			// 生成一个表格
			Sheet sheet = workbook.createSheet(title);
			// 标题
			List<String> exportFieldTitle = new ArrayList<String>();
			List<Integer> exportFieldWidth = new ArrayList<Integer>();// 列宽
			List<String> exportFields = new ArrayList<String>();// 属性集

			// 拿到所有列名，以及导出的字段的get方法
			Map<String, Method> convertMethod = new HashMap<String, Method>();

			// 得到所有字段
			Field[] fields = pojoClass.getDeclaredFields();
			Class<?> superClz = pojoClass.getSuperclass();
			if (superClz != null) {
				fields = ArrayUtils.addAll(fields, superClz.getDeclaredFields());
			}
			// 遍历整个filed
			for (int i = 0; i < fields.length; i++) {
				Field field = fields[i];
				Excel excel = field.getAnnotation(Excel.class);
				// 如果设置了annottion
				if (excel != null && containFeild(includeField, field.getName())) {
					// 添加到需要导出的字段的方法
					String fieldname = field.getName();
					if (StringUtils.isBlank(excel.exportProperty())) {
						// 添加到标题
						exportFieldTitle.add(excel.exportName());
						// 添加标题的列宽
						exportFieldWidth.add(Integer.parseInt(excel.exportFieldWidth()));
						// 添加列的属性
						exportFields.add(fieldname);
					} else {
						String[] ens = StringUtils.split(excel.exportName(), "|");
						String[] efws = StringUtils.split(excel.exportFieldWidth(), "|");
						String[] eps = StringUtils.split(excel.exportProperty(), "|");
						for (int j = 0; j < ens.length; j++) {
							// 对于实体类型的属性，只导出需要的属性
							if (includeField.contains(fieldname + "." + eps[j])) {
								exportFieldTitle.add(ens[j]);
								exportFieldWidth.add(Integer.parseInt(efws[efws.length - 1 > j ? j : efws.length - 1]));
								exportFields.add(fieldname + "." + eps[j]);
							}
						}
					}

					if (excel.exportConvertSign() == 1) {
						StringBuffer getConvertMethodName = new StringBuffer();
						getConvertMethodName.append(fieldname);
						getConvertMethodName.append("Convert");

						String methodName = getConvertMethodName.toString();
						/**
						 * 如果属性名称已“is”开头，启动报错，将其转换方法改为去掉is后的方法名称 2015-10-23
						 * 14:20
						 */
						if (StringUtils.startsWith(methodName, "is")) {
							methodName = methodName.substring(2, methodName.length());
							methodName = StringUtils.uncapitalize(methodName);
						}
						Method getConvertMethod = pojoClass.getMethod(methodName, new Class[] {});
						convertMethod.put(fieldname, getConvertMethod);
					}
				}
			}

			int index = 0;

			// 产生表格标题行
			Row row = sheet.createRow(index);

			for (int i = 0, exportFieldTitleSize = exportFieldTitle.size(); i < exportFieldTitleSize; i++) {
				Cell cell = row.createCell(i);
				RichTextString text = new HSSFRichTextString(exportFieldTitle.get(i));
				cell.setCellValue(text);
			}

			// 设置每行的列宽
			for (int i = 0; i < exportFieldWidth.size(); i++) {
				// 256=65280/255
				sheet.setColumnWidth(i, 256 * exportFieldWidth.get(i));
			}

			Iterator<?> dataIterator = dataSet.iterator();
			// 循环插入剩下的集合
			while (dataIterator.hasNext()) {
				// 从第二行开始写，第一行是标题
				index++;
				row = sheet.createRow(index);
				Object data = dataIterator.next();
				for (int k = 0, fSize = exportFields.size(); k < fSize; k++) {
					Cell cell = row.createCell(k);
					Object value = null;
					if (convertMethod.containsKey(exportFields.get(k))) {
						Method cm = convertMethod.get(exportFields.get(k));
						value = cm.invoke(data, new Object[] {});
					} else {
						try {
							value = PropertyUtils.getNestedProperty(data, exportFields.get(k));
						} catch (Exception e) {
							logger.error("第" + k + "列中" + exportFields.get(k) + "数据为空，" + ToStringBuilder.reflectionToString(data));
							// e.printStackTrace();
						}
					}
					if (value instanceof Double) {
						cell.setCellValue(value == null ? 0 : (Double) value);
					} else if (value instanceof Integer) {
						cell.setCellValue(value == null ? 0 : (Integer) value);
					} else {
						cell.setCellValue(value == null ? "" : value.toString());
					}
				}
			}
			workbook.write(out);
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static boolean containFeild(List<String> dataSet, String o) {
		if (dataSet.contains(o)) {
			return true;
		} else {
			int flag = 0;
			for (String collection : dataSet) {
				String[] data = StringUtils.split(collection, ".");
				if (data[0].equals(o)) {
					flag = 1;
				}
			}
			if (flag == 1) {
				return true;
			} else {
				return false;
			}
		}
	}

}
